﻿// Apache-2.0 License
// Copyright (c) 2021-2022 SuperJeff
// 微信:15906588664  QQ:1024489

using Admin.Net.InformationPlatform.Entity;
using Admin.Net.InformationPlatform.Service.SRM.Dto;
using Admin.NET.Application.Const;
using Admin.Net.InformationPlatform.Util;
using Admin.NET.Core;
using Admin.NET.Core.Service;
using Furion.DependencyInjection;
using Furion.DynamicApiController;
using Furion.EventBus;
using Furion.FriendlyException;
using Furion.Localization;
using Mapster;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Options;
using Nest;
using NewLife.Caching;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OfficeOpenXml;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Text;
using Org.BouncyCastle.Asn1.X509;
using SjEntityClass;
using SqlSugar;
using System.Runtime.InteropServices;
using System.Security.Cryptography;
using System.Text;
using System.Xml.Linq;
using static SKIT.FlurlHttpClient.Wechat.Api.Models.CgibinUserInfoBatchGetRequest.Types;
using static SKIT.FlurlHttpClient.Wechat.Api.Models.WxaBusinessGetUserPhoneNumberResponse.Types.PhoneInfo.Types;
using Furion;
using FluentFTP;

namespace Admin.Net.InformationPlatform.Service.SRM
{
    /// <summary>
    /// SRM管理
    /// </summary>
    [ApiDescriptionSettings(groups: "SRM", Name = "供应商管理", Tag = "供应商管理", Order = 200)]
    public class SrmManagementService : IDynamicApiController, ITransient
    {
        /// <summary>
        /// 单例数据链接
        /// </summary>
        ISqlSugarClient db;
        /// <summary>
        /// T100数据库
        /// </summary>
        private SqlSugarProvider _T100Db;
        private SqlSugarProvider _SjDb;
        private SqlSugarProvider _plm;
        private SqlSugarProvider _Dilon;


        /// <summary>
        /// 用户信息
        /// </summary>
        TokenUser _User;
        /// <summary>
        /// 成品仓库管理模块
        /// </summary>
        /// <param name="db"></param>
        public SrmManagementService(ISqlSugarClient db)
        {
            this.db = db;
            this._T100Db = this.db.AsTenant().GetConnection(T100Const.ConfigId);
            this._SjDb = this.db.AsTenant().GetConnection(SJDB.ConfigId);
            this._plm = this.db.AsTenant().GetConnection(PLM.ConfigId);
            this._Dilon = this.db.AsTenant().GetConnection(Dilon.ConfigId);
            // 获取 `Jwt` 存储的信息 企业编号与据点信息
            _User = Util.SjCommonUtil.GetT100UserTokenInfo(_T100Db,_Dilon);

        }



        /// <summary>
        /// 获取采购清单-分页
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [HttpPost("/SRM/GetPurchaseOrderPagedList")]
        public async Task<SqlSugarPagedList<GetPurchaseOrderOutput>> GetPurchaseOrderPagedList(GetPurchaseOrderPageListInput input)
        {
            var outpupt = await _T100Db.Queryable<PmdlT>().AS("DSDATA.PMDL_T")
            .InnerJoin<PmdoT>((l, o) => (l.Pmdlent == o.Pmdoent && l.Pmdldocno == o.Pmdodocno))
            .InnerJoin<PmdnT>((l, o, n) => (l.Pmdlent == n.Pmdnent && l.Pmdldocno == n.Pmdndocno && o.Pmdoseq == n.Pmdnseq))
            .LeftJoin<ImaalT>((l, o, n, il) => (n.Pmdnent == il.Imaalent && n.Pmdn001 == il.Imaal001 && il.Imaal002 == _User.Languages))
            .LeftJoin<OocalT>((l, o, n, il, oal) => (oal.Oocalent == o.Pmdoent && oal.Oocal001 == o.Pmdo004 && oal.Oocal002 == _User.Languages))
            .LeftJoin<PmaalT>((l, o, n, il, oal, pal) => (pal.Pmaalent == l.Pmdlent && pal.Pmaal001 == l.Pmdl004 && pal.Pmaal002 == _User.Languages))
            .LeftJoin<PmdpT>((l, o, n, il, oal, pal, dp) => (dp.Pmdpent == n.Pmdnent && dp.Pmdpdocno == n.Pmdndocno && dp.Pmdpseq == n.Pmdnseq))
            .LeftJoin<ImaaT>((l, o, n, il, oal, pal, dp, a) => (a.Imaaent == n.Pmdnent && a.Imaa001 == n.Pmdn001))
            .Where(l => l.Pmdlent == _User.Ent && l.Pmdlsite == _User.Site)
            //.Where(l => l.Pmdl004 == _User.UserId)
            //.WhereIF(_User.UserId == "635748264", l => (new string[] { "635748264", "63574611", "S4574069" }).Contains(l.Pmdl004))
            // .WhereIF(_User.UserId != "635748264", l => l.Pmdl004 == _User.UserId || l.Pmdl002 == _User.UserId)
            //.Where(l => l.Pmdl004 == _User.UserId || l.Pmdl002 == _User.UserId)
             .WhereIF(_User.RoleList == false, l => l.Pmdl004 == _User.UserId || l.Pmdl002 == _User.UserId)//采购员为当前登录人
            .WhereIF(!string.IsNullOrEmpty(input.ProductNo), (l, o, n) => (n.Pmdn001.Contains(input.ProductNo)))
            .WhereIF(!string.IsNullOrEmpty(input.ProductName), (l, o, n, il) => (il.Imaal003.Contains(input.ProductName)))
            .WhereIF(!string.IsNullOrEmpty(input.ProductSpecifications), (l, o, n, il) => (il.Imaal004.Contains(input.ProductSpecifications)))
            .WhereIF(!string.IsNullOrEmpty(input.BuyerNo), (l) => (l.Pmdl002.Contains(input.BuyerName)))
            .WhereIF(input.EndDate != null, (l) => (l.Pmdldocdt <= input.EndDate))
            .WhereIF(input.StartDate != null, (l) => (l.Pmdldocdt >= input.StartDate))
            .WhereIF(!string.IsNullOrEmpty(input.PurchaseOrderNo), (l) => (l.Pmdldocno.Contains(input.PurchaseOrderNo)))
            .WhereIF(!string.IsNullOrEmpty(input.PurchaseOrderStatus), (l, o, n) => (n.Pmdn045 == input.PurchaseOrderStatus))
            .WhereIF(!string.IsNullOrEmpty(input.Status), l => l.Pmdlstus == input.Status)
            .WhereIF(!string.IsNullOrEmpty(input.Remarks), (l, o, n) => (n.Pmdn050.Contains(input.Remarks)))
            .WhereIF(input.CompletionStatus == "Y", (l, o, n) => (new string[] { "C", "M", "X" }).Contains(l.Pmdlstus) || (new string[] { "2", "3", "5" }).Contains(n.Pmdn045) || o.Pmdo006 <= (o.Pmdo019 ?? 0))
            .WhereIF(input.CompletionStatus == "N", (l, o, n) => (new string[] { "N", "Y" }).Contains(l.Pmdlstus) && (n.Pmdn045 == "1") && o.Pmdo006 > (o.Pmdo019 ?? 0))
            .OrderBy(l => l.Pmdldocdt)
            .Select((l, o, n, il, oal, pal, dp,a) => new GetPurchaseOrderOutput
            {
                PurchaseOrderNo = l.Pmdldocno,
                BuyerNo = l.Pmdl002,
                DocumentDate = l.Pmdldocdt,
                ProductNo = n.Pmdn001,
                PurchaseOrderSeq = n.Pmdnseq.ToString(),
                Quantity = n.Pmdn007,
                SplitQuantity = o.Pmdo006,
                ProductName = il.Imaal003,
                ProductSpecifications = il.Imaal004,
                PurchaseOrderStatusNo = n.Pmdn045,
                QuantityReceived = o.Pmdo019,
                UnitNo = o.Pmdo004,
                UnitName = oal.Oocal003,
                Remarks = n.Pmdn050,
                PurchaseUnitPrice = n.Pmdn015,
                TaxRate = l.Pmdl012,
                SupplierNo = l.Pmdl004,
                SupplierAbbreviation = pal.Pmaal004,
                PurchaseOrderSeq1 = o.Pmdoseq1.ToString(),
                ReceivedQuantity = o.Pmdo015,
                RejectionQuantity = o.Pmdo016,
                WarehouseReturnAndExchangeVolume = o.Pmdo017,
                AssociatedDocNo = l.Pmdl005 == "2" ? dp.Pmdp003 : " ",
                DocumentType = l.Pmdl005,
                DrawingNumber = a.Imaa041,
                //DrawingNumber = l.Pmdl005,
            })
            .ToPagedListAsync(input.Page, input.PageSize);

            return outpupt;
        }

 

        /// <summary>
        /// 获取工单信息
        /// </summary>
        /// <returns></returns>
        [HttpPost("/SRM/GetWorkOrderInformation")]
        public async Task<List<GetWorkOrderInformationOutput>> GetWorkOrderInformation(GetWorkOrderInformationInput input)
        {

            var output = await _T100Db.Queryable<SfbaT>().AS("DSDATA.SFBA_T")
                .LeftJoin<ImaalT>((a, l) => a.Sfbaent == l.Imaalent && a.Sfba006 == l.Imaal001 && l.Imaal002 == _User.Languages)
                .Where(a => a.Sfbadocno == input.WorkOrderNo && a.Sfba008 != "3")
                .Select((a, l) => new GetWorkOrderInformationOutput
                {
                    Seq = a.Sfbaseq,
                    ProductNo = a.Sfba006,
                    ProductName = l.Imaal003,
                    ProductSpecifications = l.Imaal004,
                    QuantityToBeShipped = a.Sfba013,
                    QuantityIssued = a.Sfba016,
                    UnitName = a.Sfba014,
                    WarehouseLocation = SqlFunc.MappingColumn(default(string), @" (	
                        SELECT
                            listagg ( to_char(  ' 仓库:' ||G.INAG004 || ' 数量:' || CAST( G.INAG008 AS VARCHAR2 ( 4000 )) ||' 单位：' ||g.Inag007  ), '，' ) within GROUP ( ORDER BY G.INAG004 || ':' || CAST( G.INAG008 AS VARCHAR2 ( 4000 ) ) ) 
                        FROM
                            INAG_T g
                        WHERE
                            ( INAG008 > 0 ) 
                            AND ( ( INAGENT = A.SFBAENT ) AND ( INAGSITE = A.SFBASITE ) ) 
                            AND ( CAST( INAG004 AS VARCHAR2 ( 4000 ) ) IN ( '40', '41', '42', '49' ) ) 
                            AND ( INAG001 = A.SFBA006 )  ) ")
                    // WarehouseLocation = SqlFunc.Subqueryable<InagT>()
                    //             .Where(g => g.Inag001 == a.Sfba006)
                    //             .Where(g => (new string[] { "40", "41", "42", "49" }).Contains(g.Inag004.ToString()))
                    //             .Where(g => g.Inagent == a.Sfbaent && g.Inagsite == a.Sfbasite)
                    //             .Where(g => SqlFunc.IsNull(g.Inag008,0) > 0)
                    //             .SelectStringJoin(g => SqlFunc.MergeString(g.Inag004,"-",g.Inag008.ToString())  , ",")         //正常
                    // .SelectStringJoin(g => g.Inag004 + ":" + SqlFunc.ToString(g.Inag008), "/")  //报错 
                }).ToListAsync();




            return output;
        }

        /// <summary>
        /// 明富基 库存查询
        /// </summary>
        /// <returns></returns>
        [HttpPost("/SRM/GetMfjInventoryQuantityList")]
        public async Task<SqlSugarPagedList<GetInventoryQuantityListOutput>> GetInventoryQuantityList(GetInventoryQuantityListInput input)
        {
            var output = await _T100Db.Queryable<InagT>().AS("DSDATA.INAG_T")
            .LeftJoin<ImaalT>((g, l) => g.Inagent == l.Imaalent && g.Inag001 == l.Imaal001 && l.Imaal002 == _User.Languages)
            .Where(g => g.Inagsite == _User.Site)
            .Where(g => SqlFunc.IsNull(g.Inag008, 0) > 0)
            .Where(g => g.Inagent == _User.Ent && (new string[] { "40", "41", "42", "49" }).Contains(g.Inag004.ToString()))
            .WhereIF(!string.IsNullOrEmpty(input.WarehouseLocation), (g, l) => (g.Inag004.Contains(input.WarehouseLocation)))
            .WhereIF(!string.IsNullOrEmpty(input.ProductNo), (g, l) => (g.Inag001.Contains(input.ProductNo)))
            .WhereIF(!string.IsNullOrEmpty(input.ProductName), (g, l) => (l.Imaal003.Contains(input.ProductName)))
            .WhereIF(!string.IsNullOrEmpty(input.ProductSpecifications), (g, l) => (l.Imaal004.Contains(input.ProductSpecifications)))
            .Select((g, l) => new GetInventoryQuantityListOutput
            {
                InventoryQuantity = g.Inag008 ?? 0,
                ProductNo = g.Inag001,
                ProductName = l.Imaal003,
                WarehouseLocation = g.Inag004,
                ProductSpecifications = l.Imaal004,
                UnitName = g.Inag007
            })
            .ToPagedListAsync(input.Page, input.PageSize);


            return output;
        }


        /// <summary>
        /// 出入库清单查询-分页
        /// </summary>
        /// <returns></returns>
        [HttpPost("/SRM/GetStockInOutRecordPagedList")]
        public async Task<SqlSugarPagedList<GetStockInOutRecordPagedListOutput>> GetStockInOutRecordPagedList(GetStockInOutRecordPagedListInput input)
        {
            var output = await _T100Db.Queryable<PmdtT>().AS("DSDATA.PMDT_T")
            .InnerJoin<PmdsT>((t, s) => (t.Pmdtent == s.Pmdsent && t.Pmdtsite == s.Pmdssite && t.Pmdtdocno == s.Pmdsdocno))
             .LeftJoin<ImaalT>((t, s, il) => (t.Pmdtent == il.Imaalent && t.Pmdt006 == il.Imaal001 && il.Imaal002 == _User.Languages))
            .LeftJoin<OocalT>((t, s, il, oal) => (oal.Oocalent == t.Pmdtent && oal.Oocal001 == t.Pmdt023 && oal.Oocal002 == _User.Languages))
            .Where(t => t.Pmdtent == _User.Ent && t.Pmdtsite == _User.Site)
            .Where((t, s) => (s.Pmdsstus == "S" && (new string[] { "3", "4", "5", "6", "7", "12", "14", "20" }).Contains(s.Pmds000)))
            .Where((t, s) => s.Pmds007 == _User.UserId)
           // .Where((t, s) => s.Pmds007 == "635747109")//测试专用
            .WhereIF(!string.IsNullOrEmpty(input.ProductNo), (t, s, il) => (t.Pmdt006.Contains(input.ProductNo)))
            .WhereIF(!string.IsNullOrEmpty(input.ProductName), (t, s, il) => (il.Imaal003.Contains(input.ProductName)))
            .WhereIF(!string.IsNullOrEmpty(input.ProductSpecifications), (t, s, il) => (il.Imaal004.Contains(input.ProductSpecifications)))
            .WhereIF(input.EndDate != null, (t, s) => (s.Pmdsdocdt <= input.EndDate))
            .WhereIF(input.StartDate != null, (t, s) => (s.Pmdsdocdt >= input.StartDate))
            .Select((t, s, il, oal) => new GetStockInOutRecordPagedListOutput
            {
                Docno = t.Pmdtdocno,
                Seq = t.Pmdtseq,
                DocumentDate = s.Pmdsdocdt,
                PurchaseOrderNo = t.Pmdt001,
                PurchaseOrderSeq = t.Pmdt002,
                ProductNo = t.Pmdt006,
                ProductName = il.Imaal003,
                ProductSpecifications = il.Imaal004,
                Remarks = t.Pmdt059,
                Quantity = t.Pmdt020,
                UnitName = oal.Oocal003,
                DocTypeNo = s.Pmds000
            })
            .ToPagedListAsync(input.Page, input.PageSize);
            return output;
        }

        ///// <summary>
        ///// 获取对应仓库物料出入明细
        ///// </summary>
        ///// <returns></returns>
        //public async Task<string> GetProductInoutDetails(GetProductInoutDetailsInput input)
        //{



        //    return "";
        //}


        /// <summary>
        ///  获取采购清单带条码-分页
        /// </summary>
        /// <returns></returns>
        [HttpPost("/SRM/GetPurchaseOrderWithBarcodePagedList")]
        public async Task<SqlSugarPagedList<GetPurchaseOrderWithBarcodePagedListOuutpu>> GetPurchaseOrderWithBarcodePagedList(GetPurchaseOrderWithBarcodePagedListInput input)
        {
            var tm = _T100Db.Queryable<CbcaaucT>().AS("DSDATA.CBCAAUC_T")
                .GroupBy(c => new { c.Cbcaaucent, c.Cbcaaucsite, c.Cbcaauc010, c.Cbcaauc011 })
                .Where(c => c.Cbcaauc002 == "C" && c.Cbcaauc016 == "Y" && c.Cbcaauc009 != "C0" && c.Cbcaauc009 != "C20")
                .Select(c => new
                {
                    c.Cbcaaucent,
                    c.Cbcaaucsite,
                    c.Cbcaauc010,
                    c.Cbcaauc011,
                    BarcodeInProcess = SqlFunc.AggregateSum(c.Cbcaauc014)
                });
            var outpupt = await _T100Db.Queryable<PmdlT>().AS("DSDATA.PMDL_T")
            .InnerJoin<PmdoT>((l, o) => (l.Pmdlent == o.Pmdoent && l.Pmdldocno == o.Pmdodocno))
            .InnerJoin<PmdnT>((l, o, n) => (l.Pmdlent == n.Pmdnent && l.Pmdldocno == n.Pmdndocno && o.Pmdoseq == n.Pmdnseq))
            .LeftJoin<ImaalT>((l, o, n, il) => (n.Pmdnent == il.Imaalent && n.Pmdn001 == il.Imaal001 && il.Imaal002 == _User.Languages))
            .LeftJoin<OocalT>((l, o, n, il, oal) => (oal.Oocalent == o.Pmdoent && oal.Oocal001 == o.Pmdo004 && oal.Oocal002 == _User.Languages))
            .LeftJoin<PmaalT>((l, o, n, il, oal, pal) => (pal.Pmaalent == l.Pmdlent && pal.Pmaal001 == l.Pmdl004 && pal.Pmaal002 == _User.Languages))
            .LeftJoin(tm, (l, o, n, il, oal, pal, t) => (t.Cbcaaucent == l.Pmdlent && t.Cbcaaucsite == l.Pmdlsite && t.Cbcaauc010 == n.Pmdndocno && t.Cbcaauc011 == n.Pmdnseq.ToString()))
            .Where(l => l.Pmdlent == _User.Ent && l.Pmdlsite == _User.Site)
            .Where(l => l.Pmdlstus == "Y")//采购单已审核状态
            // .Where(l => l.Pmdl004 == _User.UserId || l.Pmdl002 == _User.UserId)--cyj
            .WhereIF(_User.RoleList == false, l => l.Pmdl004 == _User.UserId || l.Pmdl002 == _User.UserId)//采购员为当前登录人
            .Where((l, o, n) => (n.Pmdn045 == "1")) //明细未结束采购
            //.Where((l, o, n, il, oal, pal, t) => ((o.Pmdo006 ?? 0) - (o.Pmdo019 ?? 0) + (o.Pmdo017 ?? 0) - (t.BarcodeInProcess ?? 0)) > 0)
            .Where((l, o, n, il, oal, pal, t) => ((o.Pmdo006 ?? 0) - (o.Pmdo019 ?? 0) + (o.Pmdo017 ?? 0)) > 0)
            .WhereIF(!string.IsNullOrEmpty(input.ProductNo), (l, o, n) => (n.Pmdn001.Contains(input.ProductNo))) //品号
            .WhereIF(!string.IsNullOrEmpty(input.ProductName), (l, o, n, il) => (il.Imaal003.Contains(input.ProductName))) //品名
            .WhereIF(!string.IsNullOrEmpty(input.ProductSpecifications), (l, o, n, il) => (il.Imaal004.Contains(input.ProductSpecifications))) //规格
            .WhereIF(input.EndDate != null, (l) => (l.Pmdldocdt <= input.EndDate)) //结束日期
            .WhereIF(input.StartDate != null, (l) => (l.Pmdldocdt >= input.StartDate)) //开始日期
            .WhereIF(!string.IsNullOrEmpty(input.PurchaseOrderNo), (l) => (l.Pmdldocno.Contains(input.PurchaseOrderNo))) //采购单号
            .WhereIF(!string.IsNullOrEmpty(input.Remarks), (l, o, n) => (n.Pmdn050.Contains(input.Remarks))) //备注
            .OrderBy(l => input.Field== "DocumentDate"?l.Pmdldocdt: l.Pmdldocdt, input.Order== "descending" ? OrderByType.Desc:OrderByType.Asc) //采购单日期排序
            .Select((l, o, n, il, oal, pal, t) => new GetPurchaseOrderWithBarcodePagedListOuutpu
            {
                PurchaseOrderNo = l.Pmdldocno,
                BuyerNo = l.Pmdl002,
                DocumentDate = l.Pmdldocdt,
                ProductNo = n.Pmdn001,
                PurchaseOrderSeq = n.Pmdnseq.ToString(),
                Quantity = n.Pmdn007,
                SplitQuantity = o.Pmdo006,
                ProductName = il.Imaal003,
                ProductSpecifications = il.Imaal004,
                PurchaseOrderStatusNo = n.Pmdn045,
                QuantityReceived = o.Pmdo019,
                UnitNo = o.Pmdo004,
                UnitName = oal.Oocal003,
                Remarks = n.Pmdn050,
                PurchaseUnitPrice = n.Pmdn015,
                TaxRate = l.Pmdl012,
                SupplierNo = l.Pmdl004,
                SupplierAbbreviation = pal.Pmaal004,
                BarcodeInProcess = t.BarcodeInProcess ?? 0,
                BarcodePrintableQuantity = (o.Pmdo006 ?? 0) - (o.Pmdo019 ?? 0) + (o.Pmdo017 ?? 0) - (t.BarcodeInProcess ?? 0),
                PurchaseOrderSeq1 = o.Pmdoseq1.ToString(),
                ReceivedQuantity = o.Pmdo015,
                RejectionQuantity = o.Pmdo016,
                WarehouseReturnAndExchangeVolume = o.Pmdo017,
                ConnectingRodLength = n.Pmdnud001

            })
            //.OrderBuilder(input)
            .ToPagedListAsync(input.Page, input.PageSize);

            return outpupt;
        }


        /// <summary>
        /// 获取采购单对应条码信息
        /// </summary>
        /// <returns></returns>
        [HttpPost("/SRM/GetPurchaseOrderBarcodeListPagedList")]
        public async Task<SqlSugarPagedList<GetPurchaseOrderBarcodeListPagedListOutput>> GetPurchaseOrderBarcodeListPagedList(GetPurchaseOrderBarcodeListPagedListInput input)
        {
            var warehouse = _T100Db.Queryable<IncfucT>().AS("DSDATA.INCFUC_T")
                .Where(kw => kw.Incfucsite == _User.Site)
                .GroupBy(kw => new { kw.Incfuc001, kw.Incfuc002 })
                .Select(kw => new
                {
                    kw.Incfuc001,
                    kw.Incfuc002,
                    StorageSite = SqlFunc.AggregateMax(kw.Incfuc003)
                });



            var output = await _T100Db.Queryable<CbcaaucT>().AS("DSDATA.CBCAAUC_T")
                .InnerJoin<PmdoT>((c, o) => (c.Cbcaaucent == o.Pmdoent && c.Cbcaauc010 == o.Pmdodocno && c.Cbcaauc011 == o.Pmdoseq.ToString()))
                .InnerJoin<PmdnT>((c, o, n) => (o.Pmdoent == n.Pmdnent && o.Pmdodocno == n.Pmdndocno && o.Pmdoseq == n.Pmdnseq))
                .LeftJoin<ImaalT>((c, o, n, il) => (n.Pmdnent == il.Imaalent && n.Pmdn001 == il.Imaal001 && il.Imaal002 == _User.Languages))
                .LeftJoin<OocalT>((c, o, n, il, oal) => (oal.Oocalent == o.Pmdoent && oal.Oocal001 == o.Pmdo004 && oal.Oocal002 == _User.Languages))
                .LeftJoin<ImafT>((c, o, n, il, oal, imaf) => imaf.Imafent == n.Pmdnent && imaf.Imafsite == n.Pmdnsite && n.Pmdn001 == imaf.Imaf001)
                .LeftJoin(warehouse, (c, o, n, il, oal, imaf, kw) => kw.Incfuc001 == o.Pmdo001 && kw.Incfuc002 == (n.Pmdn028 == null ? imaf.Imaf091 : n.Pmdn028))
                .Where(c => c.Cbcaaucent == _User.Ent && c.Cbcaaucsite == _User.Site)
                .Where(c => c.Cbcaauc010 == input.PurchaseOrderNo && c.Cbcaauc011 == input.PurchaseOrderSeq)
                .Where(c => c.Cbcaauc016 == "Y")
                .OrderBy(c => c.Cbcaauc001)
                .Select((c, o, n, il, oal, imaf, kw) => new GetPurchaseOrderBarcodeListPagedListOutput
                {
                    BarcodeNumber = c.Cbcaauc001,
                    BarcodeQuantity = c.Cbcaauc014 ?? 0,
                    BarcodeStatusCode = c.Cbcaauc009,

                    SourceDocNo = c.Cbcaauc010,
                    SourceDocSeq = c.Cbcaauc011.ToString() ?? "",
                    ProductNumber = o.Pmdo001,
                    ProductName = il.Imaal003,
                    ProductSpecifications = il.Imaal004,
                    Unit = oal.Oocal003,
                    Remarks = n.Pmdn050,
                    BarcodeCreationDate = c.Cbcaauc008,
                    SuppliersProductionDate = c.Cbcaauc018,
                    WarehouseLocation = (n.Pmdn028 == null ? imaf.Imaf091 ?? "" : n.Pmdn028 ?? "") + ":" + kw.StorageSite ?? ""
                }).ToPagedListAsync(input.Page, input.PageSize);


            return output;
        }




        /// <summary>
        /// 采购单条码可生成数量
        /// </summary>
        /// <returns></returns>
        [HttpPost("/SRM/GetPurchaseOrderWithBarcode")]
        public async Task<GetPurchaseOrderWithBarcodeOutput> GetPurchaseOrderWithBarcode(GetPurchaseOrderWithBarcodeInput input)
        {

            var tm = _T100Db.Queryable<CbcaaucT>().AS("DSDATA.CBCAAUC_T")
                .GroupBy(c => new { c.Cbcaaucent, c.Cbcaaucsite, c.Cbcaauc010, c.Cbcaauc011 })
                .Where(c => c.Cbcaauc002 == "C" && c.Cbcaauc016 == "Y" && c.Cbcaauc009 != "C0" && c.Cbcaauc009 != "C20")
                .Select(c => new
                {
                    c.Cbcaaucent,
                    c.Cbcaaucsite,
                    c.Cbcaauc010,
                    c.Cbcaauc011,
                    BarcodeInProcess = SqlFunc.AggregateSum(c.Cbcaauc014)
                });
            var outpupt = await _T100Db.Queryable<PmdlT>().AS("DSDATA.PMDL_T")
            .InnerJoin<PmdoT>((l, o) => (l.Pmdlent == o.Pmdoent && l.Pmdldocno == o.Pmdodocno))
            .InnerJoin<PmdnT>((l, o, n) => (l.Pmdlent == n.Pmdnent && l.Pmdldocno == n.Pmdndocno && o.Pmdoseq == n.Pmdnseq))
            .LeftJoin<ImaalT>((l, o, n, il) => (n.Pmdnent == il.Imaalent && n.Pmdn001 == il.Imaal001 && il.Imaal002 == _User.Languages))
            .LeftJoin<OocalT>((l, o, n, il, oal) => (oal.Oocalent == o.Pmdoent && oal.Oocal001 == o.Pmdo004 && oal.Oocal002 == _User.Languages))
            .LeftJoin<PmaalT>((l, o, n, il, oal, pal) => (pal.Pmaalent == l.Pmdlent && pal.Pmaal001 == l.Pmdl004 && pal.Pmaal002 == _User.Languages))
            .LeftJoin(tm, (l, o, n, il, oal, pal, t) => (t.Cbcaaucent == l.Pmdlent && t.Cbcaaucsite == l.Pmdlsite && t.Cbcaauc010 == n.Pmdndocno && t.Cbcaauc011 == n.Pmdnseq.ToString()))
             .LeftJoin<ImafT>((l, o, n, il, oal, pal, t, imaf) => imaf.Imafent == n.Pmdnent && imaf.Imafsite == n.Pmdnsite && n.Pmdn001 == imaf.Imaf001)
            .Where(l => l.Pmdlent == _User.Ent && l.Pmdlsite == _User.Site)
            .Where(l => l.Pmdlstus == "Y")//采购单已审核状态
            .Where((l, o, n) => (n.Pmdn045 == "1")) //明细未结束采购
            .Where((l, o) => o.Pmdodocno == input.PurchaseOrderNo && o.Pmdoseq.ToString() == input.PurchaseOrderSeq)
            .Select((l, o, n, il, oal, pal, t, imaf) => new GetPurchaseOrderWithBarcodeOutput
            {
                PurchaseOrderNo = l.Pmdldocno,
                BuyerNo = l.Pmdl002,
                DocumentDate = l.Pmdldocdt,
                ProductNo = n.Pmdn001,
                PurchaseOrderSeq = n.Pmdnseq.ToString(),
                Quantity = n.Pmdn007,
                SplitQuantity = o.Pmdo006,
                ProductName = il.Imaal003,
                ProductSpecifications = il.Imaal004,
                PurchaseOrderStatusNo = n.Pmdn045,
                QuantityReceived = o.Pmdo019,
                UnitNo = o.Pmdo004,
                UnitName = oal.Oocal003,
                Remarks = n.Pmdn050,
                PurchaseUnitPrice = n.Pmdn015,
                TaxRate = l.Pmdl012,
                SupplierNo = l.Pmdl004,
                SupplierAbbreviation = pal.Pmaal004,
                BarcodeInProcess = t.BarcodeInProcess ?? 0,
                BarcodePrintableQuantity = o.Pmdo006 <= ((o.Pmdo019 ?? 0) + (o.Pmdo017 ?? 0)) ? 0 : ((o.Pmdo006 - (o.Pmdo019 ?? 0) + (o.Pmdo017 ?? 0) - (t.BarcodeInProcess ?? 0))),
                ReceivingWarehouse = n.Pmdn028 == null ? (imaf.Imaf091 ?? "") : n.Pmdn028,
                PurchaseOrderSeq1 = o.Pmdoseq1.ToString(),
                ReceivedQuantity = o.Pmdo015,
                RejectionQuantity = o.Pmdo016,
                WarehouseReturnAndExchangeVolume = o.Pmdo017

            }).FirstAsync();

            return outpupt;
        }




        /// <summary>
        /// 生成采购单到货条码
        /// </summary>
        /// <returns></returns>
        [HttpPost("/SRM/AddPurchaseOrderBarcode")]
        public async Task<List<AddPurchaseOrderBarcodeOutput>> AddPurchaseOrderBarcode(AddPurchaseOrderBarcode input)
        {
            //判断是否存在有效采购单
            var pmdo = await GetPurchaseOrderWithBarcode(input.Adapt<GetPurchaseOrderWithBarcodeInput>());
            if (pmdo == null)
            {
                throw Oops.Oh("未找到对应数据，请确认可生成数量是否大于0或者采购单正常！");
            }
            if (pmdo.BarcodePrintableQuantity == 0 || pmdo.BarcodePrintableQuantity < input.GeneratedQuantity)
            {
                throw Oops.Oh("条码可打印数量为0或者生成数量超过可打印数量，不能生成！");

            }

            //标准箱数量
            int standardBoxCont = 0;
            //尾数
            decimal mantissa = 0;


            //标准箱数量>=生成数量
            if (input.GeneratedQuantity <= input.StandardQuantity)
            {
                standardBoxCont = 1;
            }
            else
            {
                standardBoxCont = Convert.ToInt32(Math.Floor(input.GeneratedQuantity / input.StandardQuantity));
                mantissa = input.GeneratedQuantity - standardBoxCont * input.StandardQuantity;
            }

            string docType = $"{pmdo.ProductNo}%SRM%{DateTime.Now.ToString("yyyyMMdd")}%";
            //查询执行行数
            int cnt = 0;
            try //开启事务
            {
                List<CbcaaucT> cbcaauc = new List<CbcaaucT>();
                var guid = SjCommonUtil.GenerateNextID();

                //开启事务
                _T100Db.Ado.BeginTran();


                //流水码
                var serialCode = await _T100Db.Queryable<CbcaaucT>().Where(c => c.Cbcaauc001.Contains(docType)).MaxAsync(c => c.Cbcaauc001.Substring(c.Cbcaauc001.Length - 4, 4)) ?? "0000";

                for (int i = 0; i < standardBoxCont; i++)
                {

                    cbcaauc.Add(new CbcaaucT
                    {
                        Cbcaaucent = _User.Ent,
                        Cbcaaucsite = _User.Site,
                        Cbcaauc001 = docType + SjCommonUtil.StringSerialNumberConversion(serialCode, i + 1),
                        Cbcaauc002 = "C",
                        Cbcaauc003 = pmdo.ProductNo,
                        Cbcaauc006 = pmdo.UnitNo,
                        Cbcaauc007 = _User.UserId,
                        Cbcaauc008 = DateTime.Now,
                        Cbcaauc009 = "C2",
                        Cbcaauc010 = pmdo.PurchaseOrderNo,
                        Cbcaauc011 = pmdo.PurchaseOrderSeq,
                        Cbcaauc014 = input.StandardQuantity,
                        Cbcaauc016 = "Y",
                        Cbcaauc018 = input.SupplierProductionTime,
                        Cbcaauc027 = "1",
                        Cbcaauc028 = "1",
                        Cbcaauc030 = guid, //本次生产的批次
                        Cbcaauc021 = pmdo.ReceivingWarehouse
                    });

                }
                if (mantissa > 0)
                {
                    cbcaauc.Add(new CbcaaucT
                    {
                        Cbcaaucent = _User.Ent,
                        Cbcaaucsite = _User.Site,
                        Cbcaauc001 = docType + SjCommonUtil.StringSerialNumberConversion(serialCode, cbcaauc.Count + 1),
                        Cbcaauc002 = "C",
                        Cbcaauc003 = pmdo.ProductNo,
                        Cbcaauc006 = pmdo.UnitNo,
                        Cbcaauc007 = _User.UserId,
                        Cbcaauc008 = DateTime.Now,
                        Cbcaauc009 = "C2",
                        Cbcaauc010 = pmdo.PurchaseOrderNo,
                        Cbcaauc011 = pmdo.PurchaseOrderSeq,
                        Cbcaauc014 = mantissa,
                        Cbcaauc016 = "Y",
                        Cbcaauc018 = input.SupplierProductionTime,
                        Cbcaauc027 = "1",
                        Cbcaauc028 = "1",
                        Cbcaauc030 = guid, //本次生产的批次
                        Cbcaauc021 = pmdo.ReceivingWarehouse
                    });
                }

                cnt = await _T100Db.Insertable(cbcaauc).ExecuteCommandAsync();
                if (cnt == 0)
                {
                    throw Oops.Oh($"生成条码写入失败！");
                }
                if (cnt != cbcaauc.Count())
                {
                    throw Oops.Oh($"生成条码写入失败！");
                }
                List<AddPurchaseOrderBarcodeOutput> output = new List<AddPurchaseOrderBarcodeOutput>();
                foreach (var item in cbcaauc)
                {
                    output.Add(new AddPurchaseOrderBarcodeOutput
                    {
                        BarcodeNumber = item.Cbcaauc001,
                        BarcodeQuantity = item.Cbcaauc014 ?? 0,
                        SourceDocNo = item.Cbcaauc010,
                        SourceDocSeq = item.Cbcaauc014.ToString() ?? "",
                        ProductNumber = pmdo.ProductNo,
                        ProductName = pmdo.ProductName,
                        ProductSpecifications = pmdo.ProductSpecifications,
                        Unit = pmdo.UnitName,
                        Remarks = pmdo.Remarks,
                        SuppliersProductionDate = input.SupplierProductionTime,
                        BarcodeCreationDate = DateTime.Now,
                        // WarehouseLocation= !string.IsNullOrEmpty(item.Cbcaauc021)?"":"",
                        WarehouseLocation = await _T100Db.Queryable<IncfucT>().Where(c => c.Incfuc001 == pmdo.ProductNo && c.Incfuc002 == item.Cbcaauc021).MaxAsync(c => item.Cbcaauc021 + ":" + c.Incfuc003)
                    });
                }
                _T100Db.Queryable<IncfucT>().AS("DSDATA.INCFUC_T")
                .Where(kw => kw.Incfucsite == _User.Site)
                .GroupBy(kw => new { kw.Incfuc001, kw.Incfuc002 })
                .Select(kw => new
                {
                    kw.Incfuc001,
                    kw.Incfuc002,
                    StorageSite = SqlFunc.AggregateMax(kw.Incfuc003)
                });

                //提交事务
                _T100Db.Ado.CommitTran();

                return output;
            }
            catch (System.Exception e)
            {
                //撤回事务
                _T100Db.Ado.RollbackTran();
                throw Oops.Oh($"生成失败，失败原因:{e.Message}");
            }


        }
        /// <summary>
        /// 作废条码
        /// </summary>
        /// <returns></returns>
        [HttpPost("/SRM/DeletePurchaseBarcode")]
        public async Task<string> DeletePurchaseBarcode(DeletePurchaseBarcode input)
        {
            int cnt = 0;
            try
            {
                //开启事务
                _T100Db.Ado.BeginTran();

                cnt = await _T100Db.Updateable<CbcaaucT>().AS("DSDATA.CBCAAUC_T")
                .SetColumns(c => c.Cbcaauc016 == "D")
                .Where(c => c.Cbcaaucent == _User.Ent && c.Cbcaaucsite == _User.Site)
                .Where(c => c.Cbcaauc001 == input.BarcodeNumber)
                .Where(c => c.Cbcaauc009 == "C2")
                .Where(c => c.Cbcaauc016 == "Y")
                .ExecuteCommandAsync();

                if (cnt != 1)
                {
                    throw Oops.Oh("作废条码失败,请确认条码是否存在或者条码状态处于已打印状态或已生成状态！");
                }
                //提交事务
                _T100Db.Ado.CommitTran();
                return "作废成功";
            }
            catch (System.Exception e)
            {
                //撤回事务
                _T100Db.Ado.RollbackTran();
                throw Oops.Oh($"生成失败，失败原因:{e.Message}");
            }

        }
        /// <summary>
        /// 获取采购料号对应文件清单
        /// </summary>
        /// <returns></returns>
        [HttpPost("/SRM/GetProductFileNameList")]
        public List<GetProductFileNameListOutput> GetProductFileNameList(GetProductFileNameListInput input)
        {
            var prolist = _T100Db.Ado.SqlQuery<string>($@"
                SELECT
	                bmba003 
                FROM
	                DSDATA.BMBA_T
	                INNER JOIN DSDATA.BMAA_T ON bmaaent = bmbaent 
	                AND bmaasite = bmbasite 
	                AND bmba001 = bmaa001 
	                AND bmaastus = 'Y' 
                WHERE
	                bmbasite = '{_User.Site}'
	                AND ( BMBA006 IS NULL OR BMBA006 >= SYSDATE ) 
	                AND BMBAENT = '{_User.Ent}'
	                AND bmaa001 = '{input.ProductNo}'
	                AND bmba003 LIKE 'YY5001%'
                ");
            prolist.Add(input.ProductNo);
            List<GetProductFileNameListOutput> output = new List<GetProductFileNameListOutput>();
            //根据料号，采购单号，项次获取到唯一订单
            var outpupt1 = _T100Db.Queryable<PmdlT>().AS("DSDATA.PMDL_T")
           .InnerJoin<PmdoT>((l, o) => (l.Pmdlent == o.Pmdoent && l.Pmdldocno == o.Pmdodocno))
           .InnerJoin<PmdnT>((l, o, n) => (l.Pmdlent == n.Pmdnent && l.Pmdldocno == n.Pmdndocno && o.Pmdoseq == n.Pmdnseq))
           //.Where(l => l.Pmdlent == _User.Ent && l.Pmdlsite == _User.Site)
           .WhereIF(!string.IsNullOrEmpty(input.purchaseOrderNo), (l) => (l.Pmdldocno.Contains(input.purchaseOrderNo)))
           .WhereIF(!string.IsNullOrEmpty(input.purchaseOrderSeq), (l, o) => (o.Pmdoseq == long.Parse(input.purchaseOrderSeq)))
           .WhereIF(!string.IsNullOrEmpty(input.ProductNo), (l, o, n) => (n.Pmdn001.Contains(input.ProductNo)))
           //.WhereIF(input.documentDate != null, (l) => (l.Pmdldocdt == input.documentDate))

           .Select((l, o, n) => new GetPurchaseOrderOutput
           {
               SupplierNo = l.Pmdl004,
               Pmdlstus = l.Pmdlstus,
               DocumentDate = l.Pmdldocdt,
               SplitQuantity = o.Pmdo006,
               QuantityReceived = o.Pmdo019,
               PurchaseOrderStatusNo = n.Pmdn045,
           })
           .FirstAsync();
            //判断当前供应商是否拥有明富基角色
            var data = _Dilon.Ado.SqlQuery<string>($@"
                SELECT roleid 
from  SysUserRole where UserId=(SELECT id FROM SysUser where Account = '{input.supplierNo}') and roleid = '351840054182213' 
                
            ");
            //单据日期在三个月内，或者订单未完成
            if (outpupt1.Result.DocumentDate >= DateTime.Now.AddMonths(-3) || ((new string[] { "N", "Y" }).Contains(outpupt1.Result.Pmdlstus) && (outpupt1.Result.PurchaseOrderStatusNo == "1") && outpupt1.Result.SplitQuantity > (outpupt1.Result.QuantityReceived ?? 0)))
            {

                if (data.Count == 1)//明富基特殊处理，可以看rar文件
                {
                    foreach (var item in prolist)
                    {
                        //获取料号对应文件清单
                        var ss = _plm.Ado.SqlQuery<GetProductFileNameListOutput>($@"  
                    SELECT KEY,
	                    '{item}' AS ProductNumber,
	                    FILENAME AS FileName
                    FROM
	                    SJ1.cad_file_view 
                    WHERE
                        ITEMID LIKE '%{item}%' 
                    UNION
                    SELECT KEY,
	                    '{item}' AS ProductNumber,
	                    FILENAME AS FileName
                    FROM
	                    SJ1.DOC_FILE_VIEW_TEST 
                    WHERE
	                    ITEMID LIKE '%{item}%' 
                    ");
                        if (ss.Count > 0)
                        {
                            output.AddRange(ss);
                        }
                    }
                }
                else
                {
                    foreach (var item in prolist)
                    {
                        //获取料号对应文件清单
                        var ss = _plm.Ado.SqlQuery<GetProductFileNameListOutput>($@"  
                    SELECT KEY,
	                    '{item}' AS ProductNumber,
	                    FILENAME AS FileName
                    FROM
	                    SJ1.cad_file_view 
                    WHERE
                        ITEMID LIKE '%{item}%' and FILENAME  like '%.pdf'
                    UNION
                    SELECT KEY,
	                    '{item}' AS ProductNumber,
	                    FILENAME AS FileName
                    FROM
	                    SJ1.DOC_FILE_VIEW_TEST 
                    WHERE
	                    ITEMID LIKE '%{item}%' and FILENAME  like '%.pdf'
                    ");
                        if (ss.Count > 0)
                        {
                            output.AddRange(ss);
                        }
                    }
                }
            }
            output.ForEach(c => c.FileType = Path.GetExtension(c.FileName));
            return output;
        }
        /// <summary>
        /// 获取采购料号对应文件
        /// </summary>
        /// <returns></returns>
        [HttpPost("/SRM/GetProductFile")]
        public GetProductFileOutput GetProductFile(GetProductFileInput input)
        {
            //获取料号对应文件清单
            var file = _plm.Ado.SqlQuery<string>($@"  
                SELECT    FTPPATH
                FROM
                    SJ1.cad_file_view 
                WHERE
                    KEY='{input.Key}' and  FILENAME='{input.FileName}'
                UNION
                SELECT  FTPPATH
                FROM
                    SJ1.DOC_FILE_VIEW_TEST 
                WHERE
                    KEY='{input.Key}' and  FILENAME='{input.FileName}'
                ").FirstOrDefault();
            //判断文件是否存在   
            if (string.IsNullOrEmpty(file))
            {
                throw Oops.Oh("文件不存在！");
            }
            //ftp连接对象
            FtpClient ftp = new FtpClient(App.Configuration["FTP:Url"], App.Configuration["FTP:Name"], App.Configuration["FTP:Pwd"]);
            //ftp连接
            ftp.Connect();



            byte[] data = null;

            ftp.DownloadBytes(out data, file);
            //判断文件是否存在
            if (data == null)
            {
                throw Oops.Oh("文件不存在！");
            }


            //判断fileData文件，如果文件类型为PDF或者图片，加入生久科技  与 日期 文字水印
            if (Path.GetExtension(input.FileName).ToLower() == ".pdf" || Path.GetExtension(input.FileName).ToLower() == ".jpg" || Path.GetExtension(input.FileName).ToLower() == ".png")
            {
                if (Path.GetExtension(input.FileName).ToLower() == ".pdf")
                {
                    //pdf加水印
                    data = Util.Watermark.PdfAddWatermark(new MemoryStream(data), $"生久 {_User.UserId} {DateTime.Now}").ToArray();
                }
                else
                {
                    //增加判断windows才运行
                    if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
                    {
                        //图片加水印
                        data = Util.Watermark.ImageAddWatermark(new MemoryStream(data), $"生久 {_User.UserId} {DateTime.Now}").ToArray();
                    }

                    // data = Watermark.ImageAddWatermark(new MemoryStream(data), $"生久科技 {_User.UserId} {DateTime.Now}").ToArray();
                }
            }







            GetProductFileOutput output = new GetProductFileOutput();
            output.FileByte = data;
            return output;
        }


        #region 客户评级
        /// <summary>
        /// 获取客户评级
        /// </summary>
        /// <returns></returns>
        [HttpPost("/SRM/GetCustomerRatings")]
        public async Task<List<GetCustomerRatingsOutput>> GetCustomerRatings(GetCustomerRatingsInput input)
        {
            var output = await _SjDb.Queryable<Srmaa>()
            .Where(c => c.Srmaa002 == _User.UserId)
            .Where(c => c.Srmaa004.Contains(input.YearsMonth))
            .Select(c => new GetCustomerRatingsOutput
            {
                Category = c.Srmaa001,
                SupplierCode = c.Srmaa002,
                Supplier = c.Srmaa003,
                YearsMonth = c.Srmaa004,
                BatchToBeDelivered = c.Srmaa005,
                TimelyWarehousing = c.Srmaa006,
                AchievementRateScore = c.Srmaa007,
                ActualAverageLeadTime = c.Srmaa008,
                TheoreticalAverageLeadTime = c.Srmaa009,
                AverageCycleScore = c.Srmaa010,
                TotalPurchaseAmount = c.Srmaa011,
                OutputValue = c.Srmaa012,
                DeliveryNote = c.Srmaa013,
                OrderReturn = c.Srmaa014,
                WhetherTheSupplyIsSustainable = c.Srmaa015,
                Score = c.Srmaa016

            }).ToListAsync()
            ;
            return output;
        }
        #endregion




    }
}